/*
 * This is a Singleton class used for parsing each SQL query. The rules are stored in an xml
 * file. The xml is parsed and stored as objects in memory.  The parse method converts the query
 * into fragments and then into items. The whole tree of fragments & items is stored in the Query
 * object and returned
 */
package com.querie.parser;

import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.List;
import java.util.ArrayList;
import java.util.Collections;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;

import com.querie.parser.exception.MalformedQueryException;
import com.querie.pojo.*;
import com.querie.db.DBMSConnection;
import com.querie.util.QryParameters;

/**
 *
 * @author a-skoshy
 */
public class ParserFactory {

    private static class Holder {

        static final ParserFactory instance = new ParserFactory();
    }
    private List<Keyword> keywords;
    private RuleDictionary ruleDict;
    private QueryParser qp;
    //private Connection con;


    /*  A list where the position is the order in the query string and the start & stop is part of the object.
    Need to find a way to build it out
    SELECT yourcolumns
    FROM tablenames
    JOIN tablenames
    WHERE condition
    GROUP BY yourcolumns
    HAVING aggregatecolumn condition
    ORDER BY yourcolumns
     */

    /* The constructor parses the xml with the rules and converts it into in memory of objects
     *
     */
    private ParserFactory() {
        qp = new QueryParser();
        qp.parseXML();
        keywords = qp.getKeywords();
        ruleDict = new RuleDictionary(qp.getRules());
        //System.out.println("keywords:" + keywords + "   " + "RuleDictionary:" + ruleDict);


    }

    public static ParserFactory getInstance() {
        return Holder.instance;

    }

    /* Each query is parsed into it component fragments. It further processes and converts it into
     * items for collaboration. The whole hierarchy of items are stored in the Query parameter.
     *
     * */
    public void parse(Query query, Connection con) throws MalformedQueryException, SQLException {
        List<QueryKeyword> keyList = new ArrayList<QueryKeyword>();
        getGroups(query, keyList);
        FragmentParams frag = getFragments(query.getQuery(), keyList);
        query.setFragments(frag);
        query.setTables(getTables(frag, con));
        getColumns(frag, query, con);
        if (frag.getJoinFrag() != null) {
            getWhereConditions(frag, query, con);
        }
        //System.out.println(query);
    }

    /* The Strings between the SQL keywords in the query is extracted and stored as Fragment Params
     * The start and stop position of each keyword is found and this is used to extract the columns
     * , tables, where clause, group caluse, order by clause & having clause.
     * The similarity caluclation currently is based only on columns, tables & where clause.
     */
    private FragmentParams getFragments(String query, List<QueryKeyword> keyList)
            throws MalformedQueryException {

        FragmentParams frag = new FragmentParams();
        QueryKeyword currentQueryKeyWord = null;
        QueryKeyword nextQueryKeyWord = null;
        String nextKeyWord;
        String currentKeyWord;
        Rule r;
        List<String> stopKeyList;
        String stopKey = null;
        int startPos;
        int endPos;
        String fragment = null;
        String ruleName = null;

        int size = keyList.size();
        if (size < 2) {
            throw new MalformedQueryException("SELECT is not not detected");
        }

        for (int i = 0; i < keyList.size(); i++) {

            currentQueryKeyWord = keyList.get(i);
            currentKeyWord = currentQueryKeyWord.getKeyword();
            if ((i + 1) < size) {
                nextQueryKeyWord = keyList.get(i + 1);
                nextKeyWord = nextQueryKeyWord.getKeyword();
            } else {
                nextKeyWord = "END";
            }


            r = ruleDict.getKeywordRule(currentKeyWord);
            //System.out.println(r);
            stopKeyList = r.getEndKeyList();
            for (int j = 0; j < stopKeyList.size(); j++) {
                stopKey = stopKeyList.get(j);
                if (stopKey.equals(nextKeyWord)) {
                    break;
                } else if (j + 1 == stopKeyList.size()) {
                    throw new MalformedQueryException();
                }
            }

            startPos = currentQueryKeyWord.getEndChar();
            if (nextKeyWord.equals("END")) {
                endPos = query.length();
            } else {
                endPos = nextQueryKeyWord.getStartChar();
            }

            if (endPos == query.length()) {
                fragment = query.substring(startPos);
            } else {
                fragment = query.substring(startPos, endPos).trim();
            }

            ruleName = r.getName();

            if (ruleName.equals("COLUMNSTRING")) {
                frag.setColumnFrag(fragment);
            } else if (ruleName.equals("TABLESTRING")) {
                frag.setTableFrag(fragment);
            } else if (ruleName.equals("WHERESTRING")) {
                frag.setJoinFrag(fragment);
            } else if (ruleName.equals("ORDERBYSTRING")) {
                frag.setOrderFrag(fragment);
            } else if (ruleName.equals("GROUPBYSTRING")) {
                frag.setGroupFrag(fragment);
            } else if (ruleName.equals("HAVINGSTRING")) {
                frag.setHavingFrag(fragment);
            }


        }

        return frag;
    }

    /*
     *   [ FROM { < table_source > } [ ,...n ] ]

    < table_source > ::=
    table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
    | view_name [ [ AS ] table_alias ]
    | rowset_function [ [ AS ] table_alias ]
    | OPENXML
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
    | < joined_table >

    < joined_table > ::=
    < table_source > < join_type > < table_source > ON < search_condition >
    | < table_source > CROSS JOIN < table_source >
    | < joined_table >

    < join_type > ::=
    [ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } ]
    [ < join_hint > ]
    JOIN

    NOT CONSIDERING WITH

     */

    /* This method find the tables in the query and returns them as list of Tables.
    The Table contains the tablename, the id from the target tables and any alias
    that was specified in the query. The table names are compared against the schema that
     * is stored in the database.
     *
     */
    private List<Table> getTables(FragmentParams frag, Connection con) throws SQLException {
        String tableName = null;
        String alias = null;
        String tableID = null;
        String oneTableString = null;
        int index;
        String[] splitComponents;
        ResultSet rs0 = null;
        Table tab = null;

        //Connection con = DBMSConnection.getConnection();
        String strSQL = "SELECT TABLEID FROM TARGETTABLES WHERE TABLENAME= ?";
        PreparedStatement tblStmt = con.prepareStatement(strSQL);

        List tables = new ArrayList<Table>();
        String tablesString = frag.getTableFrag();
        String[] tableNames = tablesString.split(",");
        for (int i = 0; i < tableNames.length; i++) {
            tableName = null;
            alias = null;
            oneTableString = tableNames[i].toUpperCase().trim();
            index = oneTableString.indexOf(" AS ");
            if (index != -1) {
                splitComponents = oneTableString.split(" AS ");
                tableName = splitComponents[0].trim();
                //oneTableString.substring(0, asIndex).trim();
                alias = splitComponents[1].trim();
            } else {
                index = oneTableString.indexOf(" ");
                if (index != -1) {
                    splitComponents = oneTableString.split(" ");
                    tableName = splitComponents[0].trim();
                    //oneTableString.substring(0, asIndex).trim();
                    alias = splitComponents[1].trim();
                } else {
                    tableName = oneTableString.trim();
                }
            }
            //System.out.println("Table: ID:" + tableID + "|name:" + tableName + "|alias:" + alias + "\n");
            tblStmt.setString(1, tableName); //tableid
            //tblStmt = con.createStatement();
            //System.out.println(strSQL + "'"+ tableName+ "'");
            rs0 = tblStmt.executeQuery();
            if (rs0.next()) {
                tableID = rs0.getString(1);
            } else {
                tableID = null;
            }
            tab = new Table(tableID, tableName, alias);
            //System.out.println("Table:" + tableID + "|" + tableName + "|" + alias + "\n");
            //rs0.close();
            //rs0 = null;

            DBMSConnection.safeClose(rs0, null, null);
            tables.add(tab);
        }

        DBMSConnection.safeClose(null, tblStmt, null);
        return tables;
    }

    /* This method find the columns in the query and sets them as a list of Colimns in the Query
    Object. The Column contains the columnname, the id from the target column tables and any alias
    that was specified in the query. The column names are compared against the schema that
     * is stored in the database.
     *
     */
    private void getColumns(FragmentParams frag, Query qry, Connection con) throws SQLException {
        String colName = null;
        String alias = null;
        String colID = null;
        String onecolString = null;
        int index;
        String[] splitComponents;
        ResultSet rs0 = null;
        Column col = null;
        Table matchTable;
        String tableId = null;

        HashMap<String, Table> map = convertListToMap(qry.getTables());
        String tblStrs = convertListToString(qry.getTables());
        QryParameters qParam = QryParameters.getInstance();
        String qualifier = qParam.getValue("SQLCOLQUALIFIER");
        if (qualifier == null) {
            qualifier = "no";
        }

        //Connection con = DBMSConnection.getConnection();
        String strSQL = "SELECT COLUMNID FROM TARGETCOLUMNS WHERE TABLEID = ? AND COLUMNNAME= ?";
        PreparedStatement tblStmt = con.prepareStatement(strSQL);

        strSQL = "SELECT COLUMNID, A.TABLEID FROM TARGETCOLUMNS A , TARGETTABLES B " + " WHERE A.TABLEID = B.TABLEID AND COLUMNNAME= ? AND TABLENAME IN (" + tblStrs + ")";
        //System.out.println("Colstmt:" + strSQL);
        PreparedStatement colStmt = con.prepareStatement(strSQL);

        strSQL = "SELECT COUNT(*) FROM TARGETCOLUMNS A , TARGETTABLES B " + " WHERE A.TABLEID = B.TABLEID AND COLUMNNAME= ? AND TABLENAME IN (" + tblStrs + ")";
        //PreparedStatement colCntStmt = con.prepareStatement(strSQL);


        List cols = new ArrayList<Column>();
        String colsString = frag.getColumnFrag().toUpperCase();

        // The column is of the form top 20 *
        /*if (colsString.trim().matches("[tT][Oo][Pp]\\s*[0-9]+\\s*\\*")) {
        cols.add(new Column(null, null, colsString, null));
        qry.setColumns(cols);
        return;
        } else */ if (colsString.trim().toUpperCase().startsWith("TOP")) {
            //System.out.println("Before top action: " + colsString);
            Pattern p = Pattern.compile("TOP\\s*[0-9]+\\s*");
            Matcher m = p.matcher(colsString);
            if (m.find()) {
                int endIndex = m.end();
                colName = colsString.substring(0, endIndex);
                if (qualifier.equals("yes")) {
                    cols.add(new Column(null, null, colName, null));
                }
                colsString = colsString.substring(endIndex).trim();
                //System.out.println("After top action: " + colName + colsString);
            }
        }

        if (colsString.startsWith("DISTINCT")) {
            //System.out.println("Before distinct action: " + colsString);
            Pattern p = Pattern.compile("DISTINCT\\s*");
            Matcher m = p.matcher(colsString);
            if (m.find()) {
                int endIndex = m.end();
                colName = colsString.substring(0, endIndex);
                if (qualifier.equals("yes")) {
                    cols.add(new Column(null, null, colName, null));
                }
                colsString = colsString.substring(endIndex).trim();
                //System.out.println("After distinct action: " + colName + onecolString);
            }
        }

        if (colsString.trim().equals("*")) {
            //System.out.println("Before distinct action: " + colsString);
            String exCols = "";
            int i = 0;
            for (Iterator it = map.keySet().iterator(); it.hasNext();) {
                if (i == 0) {
                    exCols = exCols + it.next() + ".* ";
                } else {
                    exCols = exCols + "," + it.next() + ".* ";
                }

                i++;
            }
            colsString = exCols;
            //System.out.println("Star burst " + colsString);
        }

        String[] colNames = colsString.split(",");
        for (int i = 0; i < colNames.length; i++) {
            alias = null;
            //System.out.println("Alias at start of loop:" + alias);
            onecolString = colNames[i].toUpperCase().trim();
            index = onecolString.indexOf("(");
            if (index == -1) { //checking if there is a function or complex calc in play, skipping
                index = onecolString.indexOf(".");
                if (index != -1) {
                    splitComponents = onecolString.split("\\.");

                    alias = splitComponents[0].trim();
                    //onecolString.substring(0, asIndex).trim();
                    colName = splitComponents[1].trim();
                } else {
                    colName = onecolString.trim();
                }
            } else {
                colName = onecolString.trim();
            }
            //System.out.println("Alias at miidle of loop:" + alias);

            if (alias != null) {
                if (colName.trim().equals("*")) {
                    colName = onecolString.trim();
                    //System.out.println("Star burst single " + colName);
                } else {
                    matchTable = map.get(alias);
                    if (matchTable != null) {
                        tableId = matchTable.getTableID();
                        tblStmt.setString(1, tableId);
                        tblStmt.setString(2, colName);
                        rs0 = tblStmt.executeQuery();
                        if (rs0.next()) {
                            colID = rs0.getString(1);
                        } else {

                            colID = null;
                        }
                    } else {  //Incase some wierd things like top 10 ti.primtarget - Not handling cleaning now
                        colID = null;
                    }
                }

            } else {
                colStmt.setString(1, colName);
                rs0 = colStmt.executeQuery();
                if (rs0.next()) {
                    colID = rs0.getString(1);
                    tableId = rs0.getString(2);
                } else {
                    colID = null;
                }
            }
            //System.out.println("col:" + colID + "|" + colName + "|" + alias + "\n");
            col = new Column(tableId, colID, colName, alias);
            //System.out.println("col:" + colID + "|" + colName + "|" + alias + "\n");
            //rs0.close();
            //rs0 = null;

            DBMSConnection.safeClose(rs0, null, null);
            cols.add(col);
        }

        qry.setColumns(cols);
        DBMSConnection.safeClose(null, colStmt, null);
        DBMSConnection.safeClose(null, tblStmt, null);
    }

    /* This method generalizes the where condition and replaces based on the following
     * Replace IN with MEMBIN
     * Replace NOT IN MEMBNOTIN
     * Number to NUM
     * String to STR
     * Between to Range
     * = to Equ
     * != NONEQU
     * <=, >=. < , > to compare
     * LIKE to PATMATCH
     * 
     * SEMANTIC MATCHING HANDLING not implemented
     */
    private String generalizeWhere(String whereString) {
        String genralizedString = whereString;
        List<ReplaceWord> rWords = qp.getReplaceWords();
        ReplaceWord word = null;
        Pattern p = null;
        Matcher m = null;

        for (int i = 0; i < rWords.size(); i++) {
            word = rWords.get(i);
            p = Pattern.compile(word.getRegexp());
            m = p.matcher(genralizedString);
            genralizedString = m.replaceAll(word.getReplacement());
            //System.out.println(word + "gen:" + genralizedString);

        }

        //System.out.println("General Where String: " + genralizedString);

        return genralizedString;

    }

    /* This method find the where clauses in the query and sets them as a list of Where in the Query
    Object. The column names in the where is converted into table.column format and the order of the
     * fields changed to be in a descending sort order
     *
     */
    private void getWhereConditions(FragmentParams frag, Query qry, Connection con) throws SQLException {
        WCondition cond = null;
        LogicWord lWord = null;
        List<LogicWord> lWords = new ArrayList<LogicWord>();

        HashMap<String, Table> map = convertListToMap(qry.getTables());
        String tblStrs = convertListToString(qry.getTables());

        List conds = new ArrayList<WCondition>();
        String whereString = frag.getJoinFrag().trim().toUpperCase();
        whereString = generalizeWhere(whereString);

        Pattern p = Pattern.compile("(AND|OR)|(NOT)|\\(|\\)");
        Matcher m = p.matcher(whereString);
        while (m.find()) {
            lWord = new LogicWord(m.group(), m.start(), m.end());
            //System.out.println(lWord);
            lWords.add(lWord);
        }

        Collections.sort(lWords);

        int startPos = 0;
        int endPos = 0;
        String condString = null;
        String convString = null;
        String keyWord = null;
        for (int i = 0; i < lWords.size(); i++) {
            lWord = lWords.get(i);
            keyWord = lWord.getLogicWord();
            endPos = lWord.getStartChar();
            //System.out.println(whereString + " " + startPos + " " + endPos);
            condString = whereString.substring(startPos, endPos).trim();
            if (condString.trim().equals("")) {
                startPos = lWord.getEndChar();
                continue;
            }
            //System.out.println(condString);
            convString = convertCondition(condString, map, tblStrs, con);
            //System.out.println("Coverted:" + convString);
            startPos = lWord.getEndChar();
            //System.out.println("keyword:" + keyWord + "start:" + endPos + "end:"+ startPos);
            cond = new WCondition(condString, convString);
            conds.add(cond);

        }

        whereString = whereString.substring(startPos);
        condString = whereString;
        if (!condString.trim().equals("")) {
            //System.out.println("Last cond:" + condString);
            convString = convertCondition(condString, map, tblStrs, con);
            //System.out.println("Last Coverted:" + convString);
            cond = new WCondition(condString, convString);
            conds.add(cond);
        }

        qry.setConditions(conds);
    }

    private String convertCondition(String condString, HashMap<String, Table> map, String tblStrs, Connection con)
            throws SQLException {
        String convCondition = condString;
        String s = null;
        String regexp = null;
        String colID = null;
        Pattern p = null;
        Matcher m = null;

        //System.out.println("Inside converting" + condString);

        String[] splitString = condString.trim().split("\\s+");
        String[] replacement = new String[splitString.length];

        for (int i = 0; i < splitString.length; i++) {
            s = splitString[i].trim();
            if (i == 0 || i == 2) {
                //System.out.println(s);
                regexp = "STR|NUM|HEXNUM|STRRANGE|NUMRANGE|HEXNUMRANGE|PATMATCH";
                if (s.matches(regexp)) {
                    replacement[i] = splitString[i];
                    continue;
                } else {
                    colID = findColID(s, map, tblStrs, con);
                    if (colID != null) {
                        /*p = Pattern.compile("\\s+(" +splitString[i]+")\\s+");
                        m = p.matcher(convCondition);
                        convCondition = m.replaceFirst(" " + colID+ " ");
                        System.out.println(convCondition);*/
                        replacement[i] = colID;


                    } else {
                        replacement[i] = splitString[i];
                    }
                }

            } else {
                replacement[i] = splitString[i];
            }
        }
        if (splitString.length == 3) {
            if (replacement[0].compareTo(replacement[2]) == 1) {
                convCondition = replacement[2] + " " + replacement[1] + " " + replacement[0];
            } else {
                convCondition = replacement[0] + " " + replacement[1] + " " + replacement[2];
            }

        } else if (splitString.length == 2) {
            convCondition = replacement[0] + " " + replacement[1];
        }


        return convCondition;

    }

    //helper method
    private String findColID(String column, HashMap<String, Table> map, String tblStrs, Connection con) throws SQLException {
        int index = 0;
        String[] splitComponents = null;
        String alias = null;
        String colName = null;
        Table matchTable = null;
        String tableId = null;
        ResultSet rs0 = null;
        String colID = null;

        //Connection con = DBMSConnection.getConnection();
        String strSQL = "SELECT COLUMNID FROM TARGETCOLUMNS WHERE TABLEID = ? AND COLUMNNAME= ?";
        PreparedStatement tblStmt = con.prepareStatement(strSQL);

        strSQL = "SELECT COLUMNID, A.TABLEID FROM TARGETCOLUMNS A , TARGETTABLES B " + " WHERE A.TABLEID = B.TABLEID AND COLUMNNAME= ? AND TABLENAME IN (" + tblStrs + ")";
        PreparedStatement colStmt = con.prepareStatement(strSQL);

        index = column.indexOf(".");
        if (index != -1) {
            splitComponents = column.split("\\.");
            alias = splitComponents[0].trim();
            //onecolString.substring(0, asIndex).trim();
            colName = splitComponents[1].trim();
        } else {
            colName = column.trim();
        }
        //System.out.println("Alias at miidle of loop:" + alias);

        if (alias != null && (alias.toUpperCase().indexOf("DBO") == -1)) {
            matchTable = map.get(alias);
            if (matchTable != null) {
                tableId = matchTable.getTableID();
                tblStmt.setString(1, tableId);
                tblStmt.setString(2, colName);
                rs0 = tblStmt.executeQuery();
                if (rs0.next()) {
                    colID = rs0.getString(1);
                } else {
                    colID = null;
                }
            } else {  //Incase it is some wierd function type of thing (Eg. atan(-1*so.ecoeff_2/so.ecoeff_1)>-0.001)
                colName = column.trim();
                colStmt.setString(1, colName);
                rs0 = colStmt.executeQuery();
                if (rs0.next()) {
                    colID = rs0.getString(1);
                    tableId = rs0.getString(2);
                } else {
                    colID = null;
                }
            }

        } else {
            colStmt.setString(1, colName);
            rs0 = colStmt.executeQuery();
            if (rs0.next()) {
                colID = rs0.getString(1);
                tableId = rs0.getString(2);
            } else {
                colID = null;
            }
        }

        //System.out.println("col:" + colID + "|" + colName + "|" + alias + "\n");
        //rs0.close();
        //rs0 = null;

        DBMSConnection.safeClose(rs0, colStmt, null);
        DBMSConnection.safeClose(null, tblStmt, null);
        return colID;
    }

    //helper method for finding position of words
    private void getGroups(Query q, List<QueryKeyword> keyList) {
        Pattern p = null;
        Matcher m = null;
        String grp = null;
        int selectCount = 0;
        String query = q.getQuery();
        int cnt = 0; //Cnt is for finding the first occurence and using in the start pos & endPos
        String key;

        QueryKeyword qWord = null;

        for (int i = 0; i <
                keywords.size(); i++) {
            key = keywords.get(i).getKeyword();
            p =
                    Pattern.compile(keywords.get(i).getRegexp());
            m =
                    p.matcher(query);
            cnt =
                    0;
            while (m.find()) {

                grp = m.group();
                qWord =
                        new QueryKeyword(key, grp, m.start(), m.end());
                keyList.add(qWord);
                if (key.equals("SELECT")) {
                    selectCount++;
                }

//System.out.println(key + "|" + grp + "|" + m.start() + "|" + m.end());
                if (cnt == 0) {
                    //startPos[i] = m.start();
                    //endPos[i] = m.end();
                }

                cnt++;
            }

        }

        Collections.sort(keyList);

        /* If the query contains more than one select, then it will
        contains a subquery. It is set here */

        if (selectCount > 1) {
            q.setIsSimple(false);

        } else {
            q.setIsSimple(true);

        }

        for (QueryKeyword word : keyList) {
            //System.out.println(word);
        }

    }

    //helper method
    private HashMap<String, Table> convertListToMap(List<Table> tables) {
        HashMap<String, Table> map = new HashMap<String, Table>();
        Table tab;

        String key;

        for (int i = 0; i <
                tables.size(); i++) {
            tab = tables.get(i);
            key =
                    tab.getAlias();
            if (key == null) {
                key = tab.getTableName();
            }

            map.put(key, tab);
        }

        return map;

    }

    //helper method
    private String convertListToString(List<Table> tables) {

        String tableString = "";

        for (int i = 0; i <
                tables.size(); i++) {
            if (i == 0) {
                tableString = tableString + "'" + tables.get(i).getTableName().trim() + "'";
            } else {
                tableString = tableString + "," + "'" + tables.get(i).getTableName().trim() + "'";
            }

        }
        //tableString.substring(0, tableString.length() - 1);
        return tableString;

    }
}
